package com.site.dao.impl;

import com.site.dao.ReserveTypeDao;
import com.site.pojo.ReserveType;
import com.site.utils.JDBCUtils;
import com.site.vo.ReserveTypeAndGoodsVO;
import jdk.nashorn.internal.scripts.JD;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * FileName: ReserveTypeDaoImpl.java
 * 仓库分类表DaoImpl
 *
 * @author 田尚忠
 * @Date    2021/11/28
 * @version 1.00
 */
public class ReserveTypeDaoImpl implements ReserveTypeDao {

    @Override
    public List<ReserveType> selectAll() throws SQLException {
        List<ReserveType> list = new ArrayList<>();
        Connection conn = JDBCUtils.getConnection();
        String sql = "SELECT `rtid`, `reserve_number`, `state`, `ability`, `quantity` FROM reserve_type";
        ResultSet rs = JDBCUtils.executeQuery(conn, sql);
        while (rs.next()){
            ReserveType reserveType = new ReserveType();
            reserveType.setRtid(rs.getLong("rtid"));
            reserveType.setReserveNumber(rs.getString("reserve_number"));
            reserveType.setState(rs.getString("state"));
            reserveType.setAbility(rs.getLong("ability"));
            reserveType.setQuantity(rs.getLong("quantity"));
            list.add(reserveType);
        }
        JDBCUtils.close(rs,null,conn);
        return list;
    }

    @Override
    public ReserveType selectById(int id) throws SQLException {
        ReserveType reserveType = new ReserveType();
        Connection conn = JDBCUtils.getConnection();
        String sql = "SELECT `rtid`, `reserve_number`, `state`, `ability`, `quantity` FROM reserve_type where rtid=?";
        ResultSet rs = JDBCUtils.executeQuery(conn, sql,id);
        while (rs.next()){
            reserveType.setRtid(rs.getLong("rtid"));
            reserveType.setReserveNumber(rs.getString("reserve_number"));
            reserveType.setState(rs.getString("state"));
            reserveType.setAbility(rs.getLong("ability"));
            reserveType.setQuantity(rs.getLong("quantity"));
        }
        JDBCUtils.close(rs,null,conn);
        return reserveType;
    }

    @Override
    public int addReserveType(ReserveType reserveType) {
        String sql = "INSERT INTO `hurricane_house`.`reserve_type` (`rtid`, `reserve_number`, `state`, `ability`, `quantity`) VALUES (?, ?, ?, 10000, ?);";
        Object[] params = {reserveType.getRtid(),reserveType.getReserveNumber(),reserveType.getState(),reserveType.getQuantity()};
        return JDBCUtils.executeUpdate(sql,params);
    }

    @Override
    public int addReserveType(Connection conn, ReserveType reserveType) {
        String sql = "INSERT INTO `hurricane_house`.`reserve_type` (`rtid`, `reserve_number`, `state`, `ability`, `quantity`) VALUES (?, ?, ?, 10000, ?);";
        Object[] params = {reserveType.getRtid(),reserveType.getReserveNumber(),reserveType.getState(),reserveType.getQuantity()};
        return JDBCUtils.executeUpdate(conn,sql,params);
    }

    @Override
    public int delReserveTypeById(int id) {
        String sql = "DELETE FROM reserve_type WHERE rtid = ?";
        return JDBCUtils.executeUpdate(sql,new Object[]{id});
    }

    @Override
    public int delReserveTypeById(Connection conn, int id) {
        String sql = "DELETE FROM reserve_type WHERE rtid = ?";
        return JDBCUtils.executeUpdate(conn,sql,new Object[]{id});
    }

    @Override
    public int delReserveTypeByState(int state) {
        String sql = "DELETE FROM reserve_type WHERE state = ?";
        return JDBCUtils.executeUpdate(sql,new Object[]{state});
    }

    @Override
    public int delReserveTypeByState(Connection conn, int state) {
        String sql = "DELETE FROM reserve_type WHERE state = ?";
        return JDBCUtils.executeUpdate(conn,sql,new Object[]{state});
    }

    @Override
    public int updateReserveTypeByid(int id,ReserveType reserveType) {
        String sql = "UPDATE `hurricane_house`.`reserve_type` SET `reserve_number` = ?, `state` = ?, `ability` = 10000, `quantity` = ? WHERE `rtid` = ?;";
        Object[] params = {reserveType.getReserveNumber(),reserveType.getState(),reserveType.getQuantity(),id};
        return JDBCUtils.executeUpdate(sql,params);
    }

    @Override
    public int updateReserveTypeByid(Connection conn, int id,ReserveType reserveType) {
        String sql = "UPDATE `hurricane_house`.`reserve_type` SET `reserve_number` = ?, `state` = ?, `ability` = 10000, `quantity` = ? WHERE `rtid` = ?;";
        Object[] params = {reserveType.getReserveNumber(),reserveType.getState(),reserveType.getQuantity(),id};
        return JDBCUtils.executeUpdate(conn,sql,params);
    }

    @Override
    public List<ReserveTypeAndGoodsVO> allVOCableSize() throws SQLException {
        List<ReserveTypeAndGoodsVO> list = new ArrayList<>();
        Connection conn = JDBCUtils.getConnection();
        String sql =   "SELECT cable_size.goods_id,reserve_type.rtid ,rid,reserve_type.quantity,\n" +
                " reserve.neednum,(SELECT tname FROM goods_type WHERE cable_size.typeid = goods_type.tid) AS goodsType,reserve_type.reserve_number\n" +
                " FROM reserve\n" +
                " INNER JOIN reserve_type ON reserve_type.rtid = reserve.rtid\n" +
                " INNER JOIN cable_size ON cable_size.goods_id = reserve.goods_id";
        ResultSet rs = JDBCUtils.executeQuery(conn, sql);
        while (rs.next()){
            ReserveTypeAndGoodsVO reserveTypeAndGoodsVO = new ReserveTypeAndGoodsVO();
            reserveTypeAndGoodsVO.setGoodsid(rs.getString("goods_id"));
            reserveTypeAndGoodsVO.setRtid(rs.getLong("rtid"));
            reserveTypeAndGoodsVO.setRid(rs.getLong("rid"));
            reserveTypeAndGoodsVO.setQuantity(rs.getLong("quantity"));
            reserveTypeAndGoodsVO.setNeednum(rs.getLong("neednum"));
            reserveTypeAndGoodsVO.setGoods(rs.getString("goodsType"));
            reserveTypeAndGoodsVO.setState(rs.getString("reserve_number"));
            list.add(reserveTypeAndGoodsVO);
        }
        JDBCUtils.close(rs,null,conn);
        return list;
    }

    @Override
    public List<ReserveTypeAndGoodsVO> allVOCircuitBoardSize() throws SQLException {
        List<ReserveTypeAndGoodsVO> list = new ArrayList<>();
        Connection conn = JDBCUtils.getConnection();
        String sql =   "SELECT circuit_board_size.goods_id,reserve_type.rtid ,rid,reserve_type.quantity,\n" +
                " reserve.neednum,(SELECT tname FROM goods_type WHERE circuit_board_size.type_id = goods_type.tid) AS goodsType,reserve_type.reserve_number\n" +
                " FROM reserve\n" +
                " INNER JOIN reserve_type ON reserve_type.rtid = reserve.rtid\n" +
                " INNER JOIN circuit_board_size ON circuit_board_size.goods_id = reserve.goods_id";
        ResultSet rs = JDBCUtils.executeQuery(conn, sql);
        while (rs.next()){
            ReserveTypeAndGoodsVO reserveTypeAndGoodsVO = new ReserveTypeAndGoodsVO();
            reserveTypeAndGoodsVO.setGoodsid(rs.getString("goods_id"));
            reserveTypeAndGoodsVO.setRtid(rs.getLong("rtid"));
            reserveTypeAndGoodsVO.setRid(rs.getLong("rid"));
            reserveTypeAndGoodsVO.setQuantity(rs.getLong("quantity"));
            reserveTypeAndGoodsVO.setNeednum(rs.getLong("neednum"));
            reserveTypeAndGoodsVO.setGoods(rs.getString("goodsType"));
            reserveTypeAndGoodsVO.setState(rs.getString("reserve_number"));
            list.add(reserveTypeAndGoodsVO);
        }
        JDBCUtils.close(rs,null,conn);
        return list;
    }

    @Override
    public List<ReserveTypeAndGoodsVO> allVOElectricWireSize() throws SQLException {
        List<ReserveTypeAndGoodsVO> list = new ArrayList<>();
        Connection conn = JDBCUtils.getConnection();
        String sql =   "SELECT electric_wire_size.goods_id,reserve_type.rtid,rid,reserve_type.quantity,\n" +
                "reserve.neednum,(SELECT tname FROM goods_type WHERE electric_wire_size.type_id = goods_type.tid) AS goodsType,reserve_type.reserve_number\n" +
                "FROM reserve\n" +
                "INNER JOIN reserve_type ON reserve_type.rtid = reserve.rtid\n" +
                "INNER JOIN electric_wire_size ON electric_wire_size.goods_id = reserve.goods_id";
        ResultSet rs = JDBCUtils.executeQuery(conn, sql);
        while (rs.next()){
            ReserveTypeAndGoodsVO reserveTypeAndGoodsVO = new ReserveTypeAndGoodsVO();
            reserveTypeAndGoodsVO.setGoodsid(rs.getString("goods_id"));
            reserveTypeAndGoodsVO.setRtid(rs.getLong("rtid"));
            reserveTypeAndGoodsVO.setRid(rs.getLong("rid"));
            reserveTypeAndGoodsVO.setQuantity(rs.getLong("quantity"));
            reserveTypeAndGoodsVO.setNeednum(rs.getLong("neednum"));
            reserveTypeAndGoodsVO.setGoods(rs.getString("goodsType"));
            reserveTypeAndGoodsVO.setState(rs.getString("reserve_number"));
            list.add(reserveTypeAndGoodsVO);
        }
        JDBCUtils.close(rs,null,conn);
        return list;
    }

}
